package com.shy.dao.impl;

import com.shy.dao.StudentDao;
import com.shy.model.Student;
import com.shy.util.DatabaseUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * StudentDaoImpl类，是StudentDao接口的实现类，包含了Student表相关数据库操作的实现代码
 *

 */
public class StudentDaoImpl extends BaseDaoImpl implements StudentDao {

    @Override
    public int insert(Student s) {
        return executeUpdate("insert into Student values(?,?,?,?,?)",
                 s.getSid(),
                 s.getSname(),
                 s.getSpassword(),
                 s.getSgender(),
                 s.getSbatch());
    }

    @Override
    public int update(Student s) {
        return executeUpdate("update Student set Sname=?,Spassword=? where Sid=?",s.getSname(),s.getSpassword(),s.getSid());
    }

    @Override
    public int deleteById(String id) {
        return executeUpdate("delete from Student where Sid=?",id);
    }

    @Override
    public Student getOneById(String id) {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Student where Sid=?");
            ps.setString(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                student = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return student;
    }

    @Override
    public List<Student> getAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Student");
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return list;
    }

    @Override
    public List<Student> getStudentListByBatchName(String batchName) {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Student where Sbatch=?");
            ps.setString(1, batchName);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return list;

    }

    @Override
    public List<Student> getStudentListByBatchNameAndPageNo(String batchName, int pageNo, int pageSize) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Student where Sbatch=? limit ?,?");
            ps.setString(1, batchName);
            ps.setInt(2, (pageNo - 1) * pageSize); //分页的这里是*pageSize，不是乘以固定的10
            ps.setInt(3, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return list;
    }

    @Override
    public int getBatchSize(String batchName) {
        
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int size=0;

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select count(*) from Student where Sbatch=?");
            ps.setString(1, batchName);
            rs = ps.executeQuery();
            if (rs.next()) {
                size=rs.getInt(1);
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return size;
    }
    
}
